package com.example.homeworkmutualevaluation.Dao;

import com.example.homeworkmutualevaluation.Entity.Homework;
import com.example.homeworkmutualevaluation.DTO.UndoHomework;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface HomeworkDao extends JpaRepository<Homework, Integer> {
    @Query(value = "SELECT * FROM homework WHERE `cno`=?1 ORDER BY `deadline` DESC", nativeQuery = true)
    List<Homework> findAllByCno(String cno);
    Homework findById(int id);
    @Query(value = "SELECT new com.example.homeworkmutualevaluation.DTO.UndoHomework(h.id, h.cno, c.cname, h.title, h.deadline) " +
            "FROM Homework h, Course c WHERE h.cno=c.cno AND NOT EXISTS(" +
            "SELECT s.homeworkid FROM Submit s WHERE s.sphone=?1 AND s.homeworkid=h.id" +
            ") ORDER BY h.deadline DESC")
    List<UndoHomework> findAllUndo(String sphone);
}
